package kr.or.smardi.smardi_AScan.db;

import java.text.*;
import java.util.*;

import android.content.*;
import android.database.*;
import android.database.sqlite.*;
import android.util.*;

public class DB_Adapter {
	public static final String KEY_TITLE = "title";
	public static final String KEY_BODY = "body";
	public static final String KEY_ROWID = "_id";

	private static final String TAG = "smardi_epi";
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	private static final String DATABASE_NAME = "smardi_epi";

	private static final String TABLE_USERINFORMATION = "user_information";
	private static final String TABLE_SCANRECORD = "scan_record";

	/**
	 * Database creation sql statement
	 */
	
	// 사용자 정보를 저장하는 테이블 생성 쿼리
	private static final String CREATETABLE_USERINFORMATION = "CREATE TABLE user_information" +
			" (" +
			"uid INTEGER PRIMARY KEY, " +
			"user_name TEXT, " +
			"user_gender TEXT, " +
			"user_birthday NUMERIC" +
			");";

	// 측정 결과를 저장하는 테이블 생성 쿼리
	private static final String CREATETABLE_SCANRECORD = "CREATE TABLE " +
			TABLE_SCANRECORD + // Table name
			" (" +
			"uid INTEGER PRIMARY KEY, " +
			"uid_user_information NUMERIC, " +
			"scan_value NUMERIC, " +
			"scan_time NUMERIC" +
			");";

	// private static final String DATABASE_TABLE = "notes";
	private static final int DATABASE_VERSION = 1;

	private final Context mCtx;

	/**
	 * <PRE>
	 * 1. ClassName : DatabaseHelper
	 * 2. 작성자 : 이정민
	 * 3. 작성일 : 2012.1.11
	 * </PRE>
	 * 
	 */
	private static class DatabaseHelper extends SQLiteOpenHelper {
		@SuppressWarnings("unused")
		private Context context;

		DatabaseHelper(Context _context) {
			super(_context, DATABASE_NAME, null, DATABASE_VERSION);
			context = _context;
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// 데이터베이스 생성
			db.execSQL(CREATETABLE_USERINFORMATION);
			db.execSQL(CREATETABLE_SCANRECORD);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERINFORMATION);
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_SCANRECORD);
			onCreate(db);
		}
	}

	public DB_Adapter(Context ctx) {
		this.mCtx = ctx;
	}

	public DB_Adapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();

		// mDbHelper.onUpgrade(mDb, 1, 2);
		return this;
	}

	public void close() {
		mDbHelper.close();
	}
	
	/**
	 * 사용자를 추가한다.
	 * 
	 * @param user_name 사용자 이름
	 * @param user_gender 사용자 성별
	 * @param user_birthday 사용자 생일
	 * @return
	 */
	public long addUser(String user_name, String user_gender, String user_birthday) {
		ContentValues savingValue = new ContentValues();
		
		//Owner uid를 알아냄.
		savingValue.put("user_name", user_name);
		savingValue.put("user_gender", user_gender);
		savingValue.put("user_birthday", user_birthday);
		
		return mDb.insert(TABLE_USERINFORMATION, null, savingValue);
	}
	
	/**
	 * 사용자 정보를 수정한다.
	 * 
	 * @param user_uid 사용자의 uid
	 * @param user_name 사용자 이름
	 * @param user_gender 사용자 성별
	 * @param user_birthday 사용자 생일
	 * @return
	 */
	public long changeUser(int user_uid, String user_name, String user_gender, String user_birthday) {
		
		ContentValues updateValue = new ContentValues();
		
		updateValue.put("user_name", user_name);
		updateValue.put("user_gender", user_gender);
		updateValue.put("user_birthday", user_birthday);
		
		return mDb.update(TABLE_USERINFORMATION, updateValue, "uid="+user_uid, null);
	}
	
	/**
	 * <pre>
	 * 1. MethodName : addRecord
	 * 2. ClassName : DB_Adapter
	 * 3. Comment : 측정 결과를 DB에 저장하기 위한 함수
	 * 4. 작성자 : 이정민
	 * 5. 작성일 : 2012.01.11
	 * </pre>
	 * 
	 * @param value
	 *            측정 결과값
	 * @param recordingTimestamp
	 *            UNIX_TIMESTAMP 형태의 측정 시간
	 * @param uid_user_information
	 *            user_information 테이블에서 현재 사용자의 uid
	 * @param uid_scan_position
	 *            scan_position 테이블에서 현재 측정 부위의 uid
	 * @return
	 */
	public long addRecord(int value, long recordingTimestamp, int uid_user_information) {
		ContentValues savingValue = new ContentValues();

		savingValue.put("scan_value", value);
		savingValue.put("scan_time", recordingTimestamp);
		savingValue.put("uid_user_information", uid_user_information);

		return mDb.insert(TABLE_SCANRECORD, null, savingValue);
	}
	
	/**
	 * <pre>
	 * 1. MethodName : addRecord
	 * 2. ClassName : DB_Adapter
	 * 3. Comment : 현재 시간에 대한 측정 결과를 DB에 저장하기 위한 함수
	 * 4. 작성자 : 이정민
	 * 5. 작성일 : 2012.01.11
	 * </pre>
	 * 
	 * @param value
	 *            측정 결과값
	 * @param uid_user_information
	 *            user_information 테이블에서 현재 사용자의 uid
	 * @param uid_scan_position
	 *            scan_position 테이블에서 현재 측정 부위의 uid
	 * @return
	 */
	public long addRecord(double value, int uid_user_information) {
		ContentValues savingValue = new ContentValues();
		
		savingValue.put("scan_value", value);
		savingValue.put("scan_time", new Date().getTime());
		savingValue.put("uid_user_information", uid_user_information);

		return mDb.insert(TABLE_SCANRECORD, null, savingValue);
	}

	/**
	 * <pre>
	 * 1. MethodName : deleteRecordOfUser
	 * 2. ClassName : DB_Adapter
	 * 3. Comment : 특정 사용자의 측정 결과를 삭제하기 위한 함수
	 * 4. 작성자 : 이정민
	 * 5. 작성일 : 2012.01.11
	 * </pre>
	 * 
	 * @param uid_user_information
	 *            삭제할 사용자의 uid
	 * @return 삭제 성공 여부
	 */
	public boolean deleteRecordOfUser(int uid_user_information) {
		Log.i(TAG, "Delete records of User's uid(" + uid_user_information + ").");

		return mDb.delete(TABLE_SCANRECORD, "uid_scan_record", null) > 0;
	}
	
	public boolean deleteUser(int uid_user) {
		return mDb.delete(TABLE_USERINFORMATION, "uid="+uid_user, null) > 0;
	}
	
	/**
	 * <pre>
	 * 1. MethodName : deleteRecord
	 * 2. ClassName : DB_Adapter
	 * 3. Comment : 특정 측정 결과(uid)를 삭제하기 위한 함수
	 * 4. 작성자 : 이정민
	 * 5. 작성일 : 2012.01.11
	 * </pre>
	 * 
	 * @param rowId
	 *            데이터의 uid
	 * @return 삭제 성공 여부
	 */
	public boolean deleteRecord(long rowId) {
		Log.i("Delete called", "value__" + rowId);
		return mDb.delete(TABLE_SCANRECORD, "uid" + "=" + rowId, null) > 0;
	}


	public Cursor fetchAllRecord() {
		return mDb.query(TABLE_SCANRECORD, new String[] { "uid", "uid_user_information",
				"scan_value", "scan_time" }, null, null,
				null, null, null);
	}

	/**
	 * <pre>
	 * 1. MethodName : fetchAllRecordByUser
	 * 2. ClassName : DB_Adapter
	 * 3. Comment : 특정 사용자에 대한 모든 측정 결과를 Cursor형으로 리턴
	 * 4. 작성자 : 이정민
	 * 5. 작성일 : 2012.01.11
	 * </pre>
	 * 
	 * @param uid_user_information
	 *            특정 사용자의 uid
	 * @return 검색 결과 Cursor
	 */
	public Cursor fetchAllRecordByUser(String uid_user_information) {
		return mDb.query(TABLE_SCANRECORD, new String[] { "uid", "uid_user_information",
				"scan_value", "scan_time" }, "uid_user_information=" + uid_user_information, null, null, null, null);
	}

	
	/**
	 * @param selectedUserUID 특정 사용자의 uid
	 * @param someDate 결과를 얻고 싶은 날짜 ex) 2012-01-11
	 * @return someDate에 기록된 특정 사용자의 특정 부위에 대한 값
	 */
	public Cursor fetchAllRecordByUserAtDay(int selectedUserUID, int year, int month, int day) {
		String timestampFrom = String.valueOf(getTimestamp(year + "-" + month + "-" + day + " 00:00:00"));
		String timestampTo = String.valueOf(getTimestamp(year + "-" + month + "-" + day + " 23:59:59"));
		
		return mDb.query(TABLE_SCANRECORD,
				new String[] { "uid", "scan_value", "scan_time" },
				"uid_user_information=" + selectedUserUID + " AND " +
				"scan_time BETWEEN "+timestampFrom + " AND " + timestampTo,
				null, null, null, null);
	}
	
	/**
	 * 현재에서 24시간 전 까지의 데이터를 가져온다.
	 * 
	 * @param selectedUserUID 사용자 UID
	 * @param selectedScanPlace 측정 부위
	 * @return 검색 결과
	 */
	public Cursor fetchAllRecordByUserAndScanningPointLast24hours(int selectedUserUID) {
		long now = new Date().getTime();
		String timestampFrom = String.valueOf(now - (long)24*(long)60*(long)60*(long)1000);
		String timestampTo = String.valueOf(now);
		
		return mDb.query(TABLE_SCANRECORD,
				new String[] { "uid", "scan_value", "scan_time" },
				"uid_user_information=" + selectedUserUID + " AND " +
				"scan_time BETWEEN "+timestampFrom + " AND " + timestampTo,
				null, null, null, null);
	}
	
	/**
	 * 현재에서 24시간 전 까지의 데이터를 가져온다.
	 * 
	 * @param selectedUserUID 사용자 UID
	 * @param selectedScanPlace 측정 부위
	 * @return 검색 결과
	 */
	public Cursor fetchAllRecordByUserAndScanningPointLast48hours(int selectedUserUID) {
		long now = new Date().getTime();
		String timestampFrom = String.valueOf(now - (long)48*(long)60*(long)60*(long)1000);
		String timestampTo = String.valueOf(now);
		
		return mDb.query(TABLE_SCANRECORD,
				new String[] { "uid", "scan_value", "scan_time" },
				"uid_user_information=" + selectedUserUID + " AND " +
				"scan_time BETWEEN "+timestampFrom + " AND " + timestampTo,
				null, null, null, null);
	}
	
	
	/**
	 * 현재에서 30일 전 까지의 데이터를 가져온다.
	 * 
	 * @param selectedUserUID 사용자 UID
	 * @param selectedScanPlace 측정 부위
	 * @return 검색 결과
	 */
	public Cursor fetchAllRecordByUserAndScanningPointLast30days(int selectedUserUID) {
		long now = new Date().getTime();
		
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		
		
		String fromDate = format.format(new Date(now - (long)30*(long)24*(long)60*(long)60*(long)1000).getTime());
		
		String timestampFrom = String.valueOf(getTimestamp(fromDate + " 00:00:00"));
		String timestampTo = String.valueOf(now);
		
		return mDb.query(TABLE_SCANRECORD,
				new String[] { "uid", "scan_value", "scan_time" },
				"uid_user_information=" + selectedUserUID + " AND " +
				"scan_time BETWEEN "+timestampFrom + " AND " + timestampTo,
				null, null, null, null);
	}
	
	/**
	 * 해당하는 달의 데이터를 가져온다.
	 * 
	 * @param selectedUserUID
	 * @param selectedScanPlace
	 * @param year
	 * @param month 데이터를 가져오려는 달 (1: January)
	 * @return
	 */
	public Cursor fetchAllRecordByUserAndScanningPointAtSomeMonth(int selectedUserUID, int year, int month) {
		
		GregorianCalendar someDay = new GregorianCalendar(year, month - 1, 1);
		
		@SuppressWarnings("static-access")
		int maxDay = someDay.getActualMaximum(someDay.DAY_OF_MONTH);
		
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-01");
		String fromDate = format.format(someDay.getTime());
		String timestampFrom = String.valueOf(getTimestamp(fromDate + " 00:00:00"));
		
		format = new SimpleDateFormat("yyyy-MM-");
		String toDate = format.format(someDay.getTime()) + maxDay;
		String timestampTo = String.valueOf(getTimestamp(toDate + " 00:00:00"));
				
		return mDb.query(TABLE_SCANRECORD,
				new String[] { "uid", "scan_value", "scan_time" },
				"uid_user_information=" + selectedUserUID + " AND " +
				"scan_time BETWEEN "+timestampFrom + " AND " + timestampTo,
				null, null, null, null);
	}
	
	
	/**
	 * @param uid_user_information 특정 사용자의 uid
	 * @param uid_scan_position 측정 부위의 uid
	 * @param fromDate 결과값을 얻기 위한 시작 날짜 ex) 2012-01-11
	 * @param toDate 결과값을 얻기 위한 마지막 날짜 ex) 2012-01-12
	 * @return fromDate부터 toDate까지 기록된 특정 사용자의 특정 부위에 대한 결과값
	 */
	public Cursor fetchAllRecordByUserAndScanningPointFromSomedateToSomedate(String uid_user_information, String fromDate, String toDate) {
		String timestampFrom = String.valueOf(getTimestamp(fromDate + " 00:00:00"));
		String timestampTo = String.valueOf(getTimestamp(toDate + " 23:59:59"));
		
		return mDb.query(TABLE_SCANRECORD,
				new String[] { "uid", "scan_value", "scan_time" },
				"uid_user_information=" + uid_user_information + " AND " +
				"scan_time BETWEEN "+timestampFrom + " AND " + timestampTo,
				null, null, null, null);
	}
	
	public Cursor fetchUser(int user_uid) {
		return mDb.query(TABLE_USERINFORMATION, new String[] { "uid", "user_name", "user_gender",
				"user_birthday"}, "uid=" + user_uid, null, null, null, null);
	}

	
	public Cursor getUserList () throws SQLException {

		return  mDb.query(true, TABLE_USERINFORMATION, new String[] { "user_name", "user_gender", "user_birthday", "uid" }, 
				null, 
				null, null, null, null, null);
	}

	public boolean onCreate() {
		mDbHelper = new DatabaseHelper(mCtx);
		return (mDbHelper != null);
	}
	
	private long getTimestamp(String _date) {
		try {
			SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			Date date = (Date)formatter.parse(_date);
			
			return date.getTime();
		}
		catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}
	}
}
